use lavender::db::query_statement::prelude::*;
use lavender::db::model::prelude::*;
use lavender::*;

#[test]
fn query_where(){

    // let model  = BaseModel::default();
    let res = BaseModel::find_by_pk(25,Some(lavender_query_filters!({"id" 2},{"age" > 15},{"type in (select id from type_info)"})));
    assert!(res.is_ok());    
    let maker = res.ok().unwrap();
    println!("sql:\t\t {} \nreplace:\t\t {:?}",maker.get_sql(),maker.get_bind());

    let res = BaseModel::find(lavender_query_filters!({"name" "zhunian"},{"score" >= 80}),None,None);
    assert!(res.is_ok());    
    let maker = res.ok().unwrap();
    println!("sql:\t\t {} \nreplace:\t\t {:?}",maker.get_sql(),maker.get_bind());

    let res = BaseModel::find_all(lavender_query_filters!({"name" "zhunian"},{"score" >= 80}),None,None,None,None);
    assert!(res.is_ok());    
    let maker = res.ok().unwrap();
    println!("sql:\t\t {} \nreplace:\t\t {:?}",maker.get_sql(),maker.get_bind());

    let res = BaseModel::find_all(lavender_query_filters!({"name" "zhunian"},{"score" >= 80}),Some(ColumnSqlMaker::with_keys(vec!["id","name","age"])),Some("`created` DESC"),Some(500),None);
    assert!(res.is_ok());    
    let maker = res.ok().unwrap();
    println!("sql:\t\t {} \nreplace:\t\t {:?}",maker.get_sql(),maker.get_bind());

    let res = BaseModel::find_all(lavender_query_filter!("id" >= 80),Some(ColumnSqlMaker::with_keys(vec!["id","name","age"])),Some("`created` DESC"),Some(100),Some(200));
    assert!(res.is_ok());    
    let maker = res.ok().unwrap();
    println!("sql:\t\t {} \nreplace:\t\t {:?}",maker.get_sql(),maker.get_bind());

    let res = BaseModel::select(lavender_query_filter!("id" >= 80),Some(ColumnSqlMaker::with_keys(vec!["id","name","age"])),Some("`created` DESC"),Some(100),Some(200),Some("`type`"),None);
    assert!(res.is_ok());    
    let maker = res.ok().unwrap();
    println!("sql:\t\t {} \nreplace:\t\t {:?}",maker.get_sql(),maker.get_bind());

    let res = BaseModel::select(lavender_query_filter!("id" >= 80),Some(ColumnSqlMaker::with_keys(vec!["id","name","age"])),Some("`created` DESC"),Some(100),Some(200),Some("`type`"),Some("COUNT(DISTINCT `day`) > 1"));
    assert!(res.is_ok());    
    let maker = res.ok().unwrap();
    println!("sql:\t\t {} \nreplace:\t\t {:?}",maker.get_sql(),maker.get_bind());
 
}


#[test]
fn query_insert(){
    let res = BaseModel::add(lavender_query_key_values!({"name" "zhunian"},{"sn" "123456789"},{"created" literal "UNIX_TIMESTAMP()"}));
    assert!(res.is_ok()); 
    let maker = res.ok().unwrap();
    println!("sql:\t\t {} \nreplace:\t\t {:?}",maker.get_sql(),maker.get_bind());
}

#[test]
fn query_update(){
    let res = BaseModel::update(10001,lavender_query_key_values!({"name" "zhunian"},{"sn" "123456789"},{"created" literal "UNIX_TIMESTAMP()"}),None);
    assert!(res.is_ok()); 
    let maker = res.ok().unwrap();
    println!("sql:\t\t {} \nreplace:\t\t {:?}",maker.get_sql(),maker.get_bind());

    let res = BaseModel::update_all(lavender_query_filters!({"name" "zhunian"},{"type" 88}),lavender_query_key_values!({"name" "zhunian"},{"sn" "123456789"},{"created" literal "UNIX_TIMESTAMP()"}));
    assert!(res.is_ok()); 
    let maker = res.ok().unwrap();
    println!("sql:\t\t {} \nreplace:\t\t {:?}",maker.get_sql(),maker.get_bind());
}


#[test]
fn query_upsert(){
    let res = BaseModel::upsert(lavender_query_key_values!({"name" "zhunian"},{"sn" "123456789"},{"created" literal "UNIX_TIMESTAMP()"}),lavender_query_key_values!({"name" "zhunian"},{"sn" "123456789"},{"created" literal "UNIX_TIMESTAMP()"}));
    assert!(res.is_ok()); 
    let maker = res.ok().unwrap();
    println!("sql:\t\t {} \nreplace:\t\t {:?}",maker.get_sql(),maker.get_bind());
}


#[test]
fn query_delete(){
    let res = BaseModel::delete(10001,Some(
        lavender_query_filters!(
            {"name" "zhunian"},
            {"sn" "123456789"},
            {"created < UNIX_TIMESTAMP()"}
        )     
    ));
    assert!(res.is_ok()); 
    let maker = res.ok().unwrap();
    println!("sql:\t\t {} \nreplace:\t\t {:?}",maker.get_sql(),maker.get_bind());

    let res = BaseModel::delete_all(
        lavender_query_filters!(
            {"name" "zhunian"},
            {"sn" "123456789"},
            {"created < UNIX_TIMESTAMP()"}
        ),Some(5000)   
    );
    assert!(res.is_ok()); 
    let maker = res.ok().unwrap();
    println!("sql:\t\t {} \nreplace:\t\t {:?}",maker.get_sql(),maker.get_bind());
}

type DeviceBase = (i32,String,i16,i16,i64,i64);

fn get_test_mutil_insert_data() -> Vec<DeviceBase>{
    let mut res = vec![];
    let tn = utils::timestamp();
    for i in 0..5000  {
        res.push((i as i32 ,format!("sn_{}",i*i), (i % 10) as i16,(i / 10 % 10) as i16,tn,tn + (i as i64) + 1));
    }
    res
}

#[test]
fn query_bulk_insert(){
    let mut maker = ColumnSqlMaker::with_keys(vec!["sn","type","site","created","updated"]);
    let mutil_data = get_test_mutil_insert_data();
    maker.push_values_convert(mutil_data, |item|{
        format!("{},'{}',{},{},{},{}",item.0,item.1,item.2,item.3,item.4,item.5).into()
    });
    let res = BaseModel::bulk_insert(maker);
    assert!(res.is_ok()); 
    let maker = res.ok().unwrap();
    println!("sql:\t\t {} \nreplace:\t\t {:?}",maker.get_sql(),maker.get_bind());
}
